﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMQLCHH.DAO;
using PMQLCHH.DTO;
using System.Data.OleDb;

namespace PMQLCHH.BUS
{
    class MatHangBUS
    {
        //Kha Nguyen - mat hang BUS - 6/5/2013 - begin 
        /// <summary>
        /// Them Mat hang
        /// </summary>
        /// 
        public bool ThemMatHang(MatHangDTO MH)
        {
            bool KQ = false;
            MatHangDAO MHDAO = new MatHangDAO();
            DataTable dt_mh = MHDAO.LayDanhSachMatHang();
            string MaMH = "";
            if (dt_mh.Rows.Count > 0)
            {
                DataTable dt = MHDAO.LayMaMatHangMoiNhat();
                MaMH = "MH" + (int.Parse(dt.Rows[0]["MaMatHang"].ToString().Substring(2)) + 1).ToString();
            }
            else
            {
                MaMH = "MH1000001";
            }
            MH.MaMatHang = MaMH;
            KQ = MHDAO.ThemMatHang(MH);
            return KQ;
        }

        /// <summary>
        /// Lay Danh Sach Mat hang
        /// </summary>
        /// 
        public DataTable LayDanhSachMatHang()
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDAO = new MatHangDAO();
            dt = MHDAO.LayDanhSachMatHang();
            return dt;
        }

        /// <summary>
        /// Lay Danh Sach Mat hang de ban
        /// </summary>
        /// 
        public DataTable LayDanhSachMatHangDeBan()
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDAO = new MatHangDAO();
            dt = MHDAO.LayDanhSachMatHangDeBan();
            return dt;
        }

        /// <summary>
        /// Kiem tra ma Mat Hang da bat
        /// </summary>
        /// 
        public bool MaMatHangDaBat(string MaMH)
        {
            MatHangDAO MHDao = new MatHangDAO();
            return MHDao.MaMatHangDaBat(MaMH);
        }


        /// <summary>
        /// Lay bang Mat hang
        /// </summary>
        /// 
        public DataTable LayBangMatHang()
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDAO = new MatHangDAO();
            dt = MHDAO.LayBangMatHang();
            return dt;
        }

        /// <summary>
        /// Lay Danh Sach Loai Mat hang
        /// </summary>
        /// 
        public DataTable LayDanhSachLoaiMatHang()
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDAO = new MatHangDAO();
            dt = MHDAO.LayDanhSachLoaiMatHang();
            return dt;
        }


        /// <summary>
        /// lay ma loai mat hang
        /// </summary>
        /// 
        public int LayMaLoaiMatHang(string TenLoai)
        {
            int KQ = 0;
            MatHangDAO MHDAO = new MatHangDAO();
            KQ = MHDAO.LayMaLoaiMatHang(TenLoai);
            return KQ;
        }

        /// <summary>
        /// Lay Danh Sach Don Vi Tinh
        /// </summary>
        /// 
        public DataTable LayDanhSachDonViTinh()
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDAO = new MatHangDAO();
            dt = MHDAO.LayDanhSachDonViTinh();
            return dt;
        }
        
        /// <summary>
        /// lay ma Don Vi Tinh
        /// </summary>
        /// 
        public int LayMaDonViTinh(string TenDVT)
        {
            int KQ = 0;
            MatHangDAO MHDAO = new MatHangDAO();
            KQ = MHDAO.LayMaDonViTinh(TenDVT);
            return KQ;
        }



        /// <summary>
        /// Cap Nhat Mat Hang
        /// </summary>
        ///
        public bool CapNhatMatHang(MatHangDTO MH)
        {
            bool KQ = false;
            MatHangDAO MHDAO = new MatHangDAO();
            KQ = MHDAO.CapNhatMatHang(MH);
            return KQ;
        }


        /// <summary>
        /// Xoa Mat Hang
        /// </summary>
        ///
        public bool XoaMatHang(string MaMH)
        {
            bool KQ = false;
            MatHangDAO MHDAO = new MatHangDAO();
            KQ = MHDAO.XoaMatHang(MaMH);
            return KQ;
        }


        /// <summary>
        /// Tim kiem
        /// </summary>
        ///
        public DataTable TimKiem(string TuKhoa, int LoaiMatHang)
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDao = new MatHangDAO();
            dt = MHDao.TimKiem(TuKhoa, LoaiMatHang);
            return dt;
        }

        /// <summary>
        /// Tim kiem mat hang de ban
        /// </summary>
        ///
        public DataTable TimKiemMatHangDeBan(string TuKhoa, int LoaiMatHang)
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDao = new MatHangDAO();
            dt = MHDao.TimKiemMatHangDeBan(TuKhoa, LoaiMatHang);
            return dt;
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet hoa don
        /// </summary>
        /// 
        public bool DaDatHoaDon(string MaMH)
        {
            MatHangDAO MHDao = new MatHangDAO();
            return MHDao.DaDatHoaDon(MaMH);
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet phieu nhap
        /// </summary>
        /// 
        public bool DaDatPhieuNhap(string MaMH)
        {
            MatHangDAO MHDao = new MatHangDAO();
            return MHDao.DaDatPhieuNhap(MaMH);
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet phieu xuat
        /// </summary>
        /// 
        public bool DaDatPhieuXuat(string MaMH)
        {
            MatHangDAO MHDao = new MatHangDAO();
            return MHDao.DaDatPhieuXuat(MaMH);
        }

        /// <summary>
        /// lay chi tiet Mat Hang (Theo Hoa Don)
        /// </summary>
        ///
        public DataTable LayChiTietMatHang(string MaMH)
        {
            DataTable dt = new DataTable();
            MatHangDAO MHDao = new MatHangDAO();
            dt = MHDao.LayChiTietMatHang(MaMH);
            return dt;
        }

      

        //Kha Nguyen - Mat hang BUS - 6/5/2013 - end

        //Kha nguyen - Xuat Tap Tin Excel - 7/6/2013 - begin
        /// <summary>
        /// Xuat Mat Hang Excel
        /// </summary>
        ///
        public void XuaTMatHangRaExcel(DataTable dt, string TenFile)
        {

            //Tạo các đối tượng Excel
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks oBooks;
            Microsoft.Office.Interop.Excel.Sheets oSheets;
            Microsoft.Office.Interop.Excel.Workbook oBook;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            //Tạo mới một Excel WorkBook
            oExcel.Visible = false;
            oExcel.DisplayAlerts = false;
            string Ext = ".xls";//office 97-2003
            string version = oExcel.Version;
            float iVersion = float.Parse(version);
            if (iVersion == 12.0)//phien ban 12.0 la ofice 2007 tro len
            {
                Ext = ".xlsx";
            }
            //string Ext = ".xlsx";
            oExcel.Application.SheetsInNewWorkbook = 1;
            oBooks = oExcel.Workbooks;

            oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            oSheets = oBook.Worksheets;
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);//luu vao sheet dau tien
            oSheet.Name = "MatHang";//ten sheet

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                oSheet.Cells[1, j + 1] = dt.Columns[j].ColumnName;   
            }
            

            for (int i = 1; i < dt.Rows.Count + 1; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    oSheet.Cells[i + 1, j + 1] = dt.Rows[i - 1][j].ToString();
                }
            }


            Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range("A" + 1, "XFD" + dt.Rows.Count);
            r.Columns.AutoFit(); 
            
           


            oExcel.ActiveWorkbook.SaveCopyAs(TenFile + Ext);
            oExcel.ActiveWorkbook.Saved = true;
            oExcel.Quit();
        }

        //Kha nguyen - Xuat Tap Tin Excel - 7/6/2013 - end

        //Kha nguyen - Doc Tap Tin Excel - 8/6/2013 - begin
        /// <summary>
        /// Them Mat Hang tu Excel
        /// </summary>
        ///

        public int NhapTuExcel(string DuongDan)
        {
            
            int SoLuongNhap = 0;
            try
            {
                string onlyFileExt = System.IO.Path.GetExtension(DuongDan);
                string _cn = "";
                if (onlyFileExt.Contains("xlsx"))
                {
                    _cn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DuongDan + ";" + "Extended Properties=Excel 12.0;";
                }
                else
                {
                    _cn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + DuongDan + ";" + "Extended Properties=Excel 8.0;";
                }
                OleDbConnection _con = new OleDbConnection(_cn);
                _con.Open();
                string queryString = "SELECT * FROM [MATHANG$]";
                DataTable _table = new DataTable();
                OleDbDataAdapter _da = new OleDbDataAdapter(queryString, _con);
                _da.Fill(_table);
                int iSoDong = _table.Rows.Count;
                int iSoCot = _table.Columns.Count;

                foreach (DataRow dr in _table.Rows)
                {
                    MatHangDTO MH = new MatHangDTO();
                    MH.MaMatHang = dr["MaMatHang"].ToString();
                    MH.TenMatHang = dr["TenMatHang"].ToString();
                    MH.LoaiMatHang = int.Parse(dr["LoaiMatHang"].ToString());
                    MH.DonViTinh = int.Parse(dr["DonViTinh"].ToString());
                    MH.SoLuongTon = int.Parse(dr["SoLuongTon"].ToString());
                    MH.GiaThamKhao = float.Parse(dr["GiaThamKhao"].ToString());
                    MH.Ghichu_MH = dr["GhiChu"].ToString();
                    MH.TrangThai = int.Parse(dr["TrangThai"].ToString());
                    MH.NgayLap = dr["NgayLap"].ToString();
                    MatHangDAO MHDao = new MatHangDAO();
                    if (!MHDao.KiemTraMaMatHang(MH.MaMatHang))
                    {
                        if (MHDao.NhapMatHang(MH))
                        {
                            SoLuongNhap++;
                        }
                    }
                }

                _con.Close();
            }
            catch (Exception ex)
            {

            }
            return SoLuongNhap;
        }
        //Kha nguyen - Doc Tap Tin Excel - 8/6/2013 - end


        //Kha Nguyen - Cap Nhat So Luong Ton - 14/6/2013 - begin
        /// <summary>
        /// Cap nhat so luong ton sau khi thanh toan hoa don
        /// </summary>
        ///
        public bool CapNhatSoLuongTon(string MaMH,int SoLuongTon)
        {
            bool KQ = false;
            MatHangDAO MHDAO = new MatHangDAO();
            KQ = MHDAO.CapNhatSoLuongTon(MaMH, SoLuongTon);
            return KQ;
        }

        //Kha Nguyen - Cap Nhat So Luong Ton - 14/6/2013 - end

        #region Quang Dat Them Bao Cao Mat Hang 14/06/2013

        public static DataTable Load_BaoCao_MatHang()
        {
            DataTable dt = MatHangDAO.Load_BaoCao_MatHang();
            return dt;
        }

        #endregion

    }
}
